﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TS2015.Models;
using Kendo.Mvc.UI;
using Kendo.Mvc.Extensions;
using System.Data.Entity;
using System.Data.OleDb;
using System.Data;
using System.Data.Entity.Validation;
using System.Text;

namespace TS2015.Areas.Admin.Controllers
{
    public class HighSchoolManagementController : Controller
    {
        TS2015Context db = new TS2015Context();
        bool CheckArea, CheckProvince, CheckHighSchool, CheckUniversity;
        List<string> error = new List<string>();
        string count, diachi;
        //string IDArea, IDProvince;
        // GET: Admin/HighSchoolManagement
        public JsonResult GetHighSchool()
        {
            return Json(db.TbTruongTHPTs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult HighSchool()
        {
            Database.SetInitializer<TS2015Context>(null);

            ViewBag.TbTinh = db.TbTinhs.ToList();
            ViewBag.TbKhuVuc = db.TbKhuVucs.ToList();

            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public JsonResult GetHighSchoolList()
        {
            var HighSchoolList = from a in db.TbTruongTHPTs
                                 join b in db.TbTinhs on a.MaTinh equals b.MaTinh
                                 join c in db.TbKhuVucs on a.MaKhuVuc equals c.MaKhuVuc
                                 select new { 
                                     IDTruong = a.IDTruong,
                                     MaTruong = a.MaTruong,
                                     TenTruong = a.TenTruong,
                                     MaTinh = a.MaTinh,
                                     DiaChi = a.DiaChi,
                                     TenTinh = b.TenTinh,
                                     MaKhuVuc = a.MaKhuVuc,
                                     TenKhuVuc = c.TenKhuVuc
                                 };
            return Json(HighSchoolList, JsonRequestBehavior.AllowGet);
        }
        public ActionResult EditingPopup_ReadHighSchool([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbTruongTHPTs.ToDataSourceResult(request));
        }
        public ActionResult EditingPopup_CreateHighSchool([DataSourceRequest] DataSourceRequest request, TbTruongTHPT data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbTruongTHPTs.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_UpdateHighSchool([DataSourceRequest] DataSourceRequest request, TbTruongTHPT data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyHighSchool([DataSourceRequest] DataSourceRequest request, TbTruongTHPT data)
        {
            if (data != null)
            {
                TbTruongTHPT itemdata = db.TbTruongTHPTs.First(item => item.MaTruong == data.MaTruong);
                db.TbTruongTHPTs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult ImportHighSchool(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);
                        DataTable dt = new DataTable();
                        TbTruongTHPT item;
                        oledbda.Fill(dt);
                        var ListArea = (from c in db.TbKhuVucs select c).ToList();
                        var ListHighSchool = (from c in db.TbTruongTHPTs select c).ToList();
                        var ListProvince = (from c in db.TbTinhs select c).ToList();
                        //System.IO.File.Delete(path);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("truongpt"))
                            {
                                error.Add("Cột Mã trường không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("tentruong"))
                            {
                                error.Add("Cột Tên trường không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("diachi"))
                            {
                                error.Add("Cột Địa chỉ không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("tinh"))
                            {
                                error.Add("Cột Tỉnh không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("khuvuc"))
                            {
                                error.Add("Cột Khu vực không tồn tại!");
                                goto next;
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                
                                foreach (var a in ListProvince)
                                {
                                    CheckProvince = false;
                                    if (a.MaTinh.ToLower().Trim() == dt.Rows[i]["tinh"].ToString().ToLower().Trim())
                                    {
                                        CheckProvince = true;
                                        break;
                                    }
                                }
                               
                                foreach (var a in ListArea)
                                {
                                    CheckArea = false;
                                    if (a.MaKhuVuc.ToLower().Trim() == dt.Rows[i]["khuvuc"].ToString().ToLower().Trim())
                                    {
                                        CheckArea = true;
                                        break;
                                    }
                                }
                                
                                foreach (var a in ListHighSchool)
                                {
                                    CheckHighSchool = false;
                                    if (a.MaTruong.ToLower().Trim() == dt.Rows[i]["truongpt"].ToString().ToLower().Trim() &&
                                        a.MaTinh.ToLower().Trim() == dt.Rows[i]["tinh"].ToString().ToLower().Trim())
                                    {
                                        CheckHighSchool = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                    if (CheckProvince && CheckArea && !CheckHighSchool)
                                    {
                                        item = new TbTruongTHPT();
                                        item.MaTruong = dt.Rows[i]["truongpt"].ToString().Trim();
                                        item.TenTruong = dt.Rows[i]["tentruong"].ToString().Trim();
                                        item.DiaChi = dt.Rows[i]["diachi"].ToString().Trim();
                                        item.MaTinh = dt.Rows[i]["tinh"].ToString().Trim();
                                        item.MaKhuVuc = dt.Rows[i]["khuvuc"].ToString().Trim();
                                        db.TbTruongTHPTs.Add(item);
                                        SaveChanges(db);
                                    }
                                    else
                                    {
                                        error.Add("Lỗi");
                                    }
                                //}
                                //catch
                                //{
                                //    continue;
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                            mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        private void SaveChanges(DbContext context)
        {
            try
            {
                context.SaveChanges();
            }
            catch (DbEntityValidationException ex)
            {
                StringBuilder sb = new StringBuilder();

                foreach (var failure in ex.EntityValidationErrors)
                {
                    sb.AppendFormat("{0} failed validation\n", failure.Entry.Entity.GetType());
                    foreach (var error in failure.ValidationErrors)
                    {
                        sb.AppendFormat("- {0} : {1}", error.PropertyName, error.ErrorMessage);
                        sb.AppendLine();
                    }
                }

                throw new DbEntityValidationException(
                    "Entity Validation Failed - errors follow:\n" +
                    sb.ToString(), ex
                ); // Add the original exception as the innerException
            }
        }

        #region University
        //[OutputCache(Duration=600)]
        public JsonResult GetUniversity()
        {
            return Json(db.TbTruongDHs.ToList(), JsonRequestBehavior.AllowGet);
        }
        public ActionResult University()
        {
            if (!Request.IsAjaxRequest())
                return View();

            return PartialView();
        }
        public ActionResult EditingPopup_ReadUniversity([DataSourceRequest] DataSourceRequest request)
        {
            return Json(db.TbTruongDHs.ToDataSourceResult(request));
        }

        public ActionResult EditingPopup_CreateUniversity([DataSourceRequest] DataSourceRequest request, TbTruongDH data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.TbTruongDHs.Add(data);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }

        public ActionResult EditingPopup_UpdateUniversity([DataSourceRequest] DataSourceRequest request, TbTruongDH data)
        {
            if (data != null && ModelState.IsValid)
            {
                db.Entry(data).State = EntityState.Modified;
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }
        public ActionResult EditingPopup_DestroyUniversity([DataSourceRequest] DataSourceRequest request, TbTruongDH data)
        {
            if (data != null)
            {
                TbTruongDH itemdata = db.TbTruongDHs.First(item => item.MaTruongDH == data.MaTruongDH);
                db.TbTruongDHs.Remove(itemdata);
                db.SaveChanges();
            }
            return Json(ModelState.ToDataSourceResult());
        }

        [HttpPost]
        [ValidateAntiForgeryToken]

        public ActionResult ImportUniversity(HttpPostedFileBase FileImport)
        {

            bool state = false;
            string mess = "";
            if (FileImport != null)
            {
                // thuc hien thao tac tai day
                if (FileImport.ContentLength > 0)
                {
                    string Extension = System.IO.Path.GetExtension(FileImport.FileName);
                    if (Extension == ".xls" || Extension == ".xlsx")
                    {
                        string path = string.Concat(Server.MapPath("~/Uploads/File/" + DateTime.Now.ToString("MMddyyyyHHmmssfff") + FileImport.FileName));
                        //if (System.IO.File.Exists(path))
                        //{

                        //    System.IO.File.Delete(path);
                        //}
                        FileImport.SaveAs(path);
                        OleDbConnection oledbcon = new OleDbConnection("provider=microsoft.ace.oledb.12.0;data source=" + path + ";extended properties=excel 12.0;");
                        oledbcon.Open();
                        DataTable ExcelSheets = oledbcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        string SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
                        OleDbCommand oledbcmd = new OleDbCommand("Select * from [" + SpreadSheetName + "]", oledbcon);
                        OleDbDataAdapter oledbda = new OleDbDataAdapter(oledbcmd);

                        DataTable dt = new DataTable();
                        var ListUniversity = (from c in db.TbTruongDHs select c).ToList();
                        TbTruongDH item;
                        oledbda.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            if (!dt.Columns.Contains("Mã Trường"))
                            {
                                error.Add("Cột Mã trường không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Tên Trường"))
                            {
                                error.Add("Cột Mã trường không tồn tại!");
                                goto next;
                            }
                            if (!dt.Columns.Contains("Địa chỉ"))
                            {
                                error.Add("Cột địa chỉ không tồn tại!");
                                //diachi = null;
                                //goto next;
                                
                            }
                            for (int i = 0; i < dt.Rows.Count - 1; i++)
                            {
                                foreach (var a in ListUniversity)
                                {
                                    CheckUniversity = false;
                                    if (a.MaTruongDH.ToLower().Trim() == dt.Rows[i]["Mã Trường"].ToString().ToLower().Trim())
                                    {
                                        CheckUniversity = true;
                                        break;
                                    }
                                }
                                //try
                                //{
                                if (!CheckUniversity)
                                {
                                    item = new TbTruongDH();
                                    item.MaTruongDH = dt.Rows[i]["Mã Trường"].ToString().Trim();
                                    item.TenTruongDH = dt.Rows[i]["Tên Trường"].ToString().Trim();
                                    if (!dt.Columns.Contains("Địa chỉ"))
                                    {
                                        item.DiaChi = null;
                                    }
                                    item.DiaChi = dt.Rows[i]["Địa chỉ"].ToString().Trim();
                                    db.TbTruongDHs.Add(item);
                                    SaveChanges(db);
                                }
                                else
                                {
                                    error.Add("Trường đã tồn tại!");
                                }
                                //}
                                //catch
                                //{
                                //}
                            }
                            for (int k = 0; k < error.Count; k++)
                            {
                                count += " + " + error[k].ToString() + "<br/>";
                            }
                        next:
                            state = true;
                        mess = "Thực hiện chèn thành công " + (dt.Rows.Count - error.Count) + " bản ghi!";
                            //empData["Message"] = "Lưu thành công!";
                        }
                        else
                        {
                            Response.Write("Bạn phải nhập file định dạng excel!");
                        }
                        oledbcon.Close();
                        System.IO.File.Delete(path);
                    }

                }
            }
            else
                mess = "Có lỗi.";

            return Json(new { State = state, Message = mess });
        }
        #endregion
    }
}